答應幫忙寫一個MySQL版本的全資料庫搜尋某字串
請問如何在 MySQL 資料庫中找到某字串？

情境:

當有一天想要查詢資料庫所有表格中，是不是有符合某資料的欄位
並且把它們都查詢出來。

主要邏輯:

1. 使用INFORMATION_SCHEMA.COLUMNS撈取所有的表格、欄位資料
2. 內層使用GROUP_CONCAT組合出where {欄位名稱A} = {搜尋的值} or {欄位名稱B} = {搜尋的值} or {欄位名稱C} = {搜尋的值} .....的條件
3. 外層使用GROUP_CONCAT加上union all來拼接一次性查詢結果
4. 注意因為GROUP_CONCAT會有長度限制，需要用@@local.group_concat_max_len來調整大小。可以參考
   mysql - SQL Stored Procedure - variable too short - Stack Overflow
5. 假如不需要查詢系統表格要加上where TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys' )
6. 使用動態拼接字串SQL並Execute。
7. 這版特色是可以只返回一個結果集搜尋全表格欄位。
8. 假如要搜尋整數改成 set @strSearch := N'132';就可以。

Script:

SET @@local.group_concat_max_len=100000000;
set @Search := N'高雄';
## 判斷變數類型
set @Condition := (select 
case when BINARY (@Search * 1) = BINARY @Search then
      '"bigint","decimal","double","float","int","smallint","tinyint"'
else '"char","varchar","nvarchar"'
end );
set @sql := (
select GROUP_CONCAT(DISTINCT `sql` SEPARATOR ' union all ') from (
  SELECT 
      concat(
        N'select \'',TABLE_NAME,'\' as `表格名稱` , concat( '
        
        ,GROUP_CONCAT(DISTINCT concat(' case when count(case when `',COLUMN_NAME, '` = N\'',@Search,'\' then 1 end) > 0 then  '
                                      ,'"',COLUMN_NAME,'  " else "" end'
                                     )
                    ORDER BY COLUMN_TYPE asc 
                    SEPARATOR ' , ')        
        ,' ) as `符合欄位` from ',TABLE_NAME,'  where '
        ,GROUP_CONCAT(DISTINCT concat('`',COLUMN_NAME, '` = N\'',@Search,'\'')
                    ORDER BY COLUMN_TYPE asc 
                    SEPARATOR ' or ') 
      )  as `sql`
  FROM INFORMATION_SCHEMA.COLUMNS
  where TABLE_SCHEMA not in  ('information_schema','performance_schema','mysql','sys' )
  and  @Condition like concat('%"',DATA_TYPE,'"%')
  group by TABLE_NAME) T
);

SET @sql = CONCAT("select *
                    from (
                   "
                   ,@sql
                   ,") T
                    where `符合欄位` <> ''
                    ");         

PREPARE stmt1 FROM @sql; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

DDL測試資料:

CREATE TABLE Table0 (`name` varchar(32), `age` int, `city` varchar(6))   
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;                                                                   
INSERT INTO Table0(`name`, `age`, `city`)VALUES(N'ITMan0', 31, 'Taipei'),(N'ITMan1', 32, 'Taipei');
CREATE TABLE Table1 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table1(`name`, `age`, `city`)VALUES(N'ITMan1', 31, 'Taipei'),(N'ITMan2', 32, 'Taipei');
CREATE TABLE Table2 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table2(`name`, `age`, `city`)VALUES(N'ITMan2', 31, '高雄'),(N'ITMan3', 32, 'Taipei');
CREATE TABLE Table3 (`name` varchar(32), `age` int, `city` varchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table3(`name`, `age`, `city`)VALUES(N'ITMan3', 31, '台北'),(N'ITMan4', 32, 'Taipei');
CREATE TABLE Table4 (`name` varchar(32), `age` int, `city` nvarchar(6))
ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   
INSERT INTO Table4(`name`, `age`, `city`)VALUES(N'ITMan3', 31, '台北'),(N'ITMan5', 0, 'Taipei');

得到結果:

線上測試連結

假如想要sql-server版本可以看 @純真的人 大大的文章，強烈推薦!
MSSQL如何跨庫多表查詢某個關鍵字在某個欄位

假如想要oracle版本可以看我之前寫的文章，這版本需要C#配合，之後會再優化成單純SP版本。小程式-Oracle&C#全表格查詢搜尋單一值